package cn.conac.rc.ofs.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.ofs.entity.DepartmentEntity;

@Repository
public interface DepartmentRepository extends GenericDao<DepartmentEntity, Integer> {

	/**
	* @param dutyId
	 * @return 删除数量
	 */
	@Query(value = "select a.* from ofs_department a join ofs_depart_duty b on a.id = b.depart_id where b.duty_id=:dutyId order by a.dep_num asc", nativeQuery = true)
	public List<DepartmentEntity> findByDutyId(@Param("dutyId") Integer dutyId);
	
	/**
	 * 根据baseId查询该机构的所有内设机构信息(并按照内设顺序号升序排序)
	 * @param baseId
	 * @return DutyEntity的List
	 */
	public List<DepartmentEntity> findByBaseIdOrderByDepNumAsc(Integer baseId);
	
	/**
	 * 根据baseId查询该机构的所有内设机构信息(并按照内设顺序号升序排序)
	 * @param baseId
	 * @return DutyEntity的List
	 */
	public List<DepartmentEntity> findByBaseId(Integer baseId);
	
	
	/**
	 * 根据baseId查询该机构的所有内设机构信息(并按照内设顺序号升序排序)
	 * @param baseId
	 * @return DutyEntity的List
	 */
	public List<DepartmentEntity> findByBaseIdAndParentIdOrderByDepNumAsc(Integer baseId, Integer parentId);
	/**
	 * 根据部门库Id获取没有关联职能职责的内设机构
	 * @return
	 */
	@Query(value = "select distinct od.* from ofs_organization oo left join ofs_organization_base oob on oo.BASE_ID = oob.id  left join ofs_org_status oos  on oos.id = oob.id inner join ofs_department od on oo.BASE_ID = od.org_id and ((oob.type = 1 and od.DEPT_FUNC_TYPE = '01') or (oob.type = 2 and od.DEPT_FUNC_TYPE = '11')) where od.ID not in (select distinct odd.DEPART_ID from ofs_depart_duty odd) and oob.OWN_SYS is not null   and oo.BASE_ID is not null and oob.area_id is not null and oos.status = '03' and oos.update_type <> '50' and oos.is_history = 0 and oo.id =:id ", nativeQuery = true)
	List<DepartmentEntity> finddeptWithNoDeptListByOrgId(@Param("id") Integer id);
	
	/**
	 * 根据baseId查询该机构的一级内设机构信息(并按照内设顺序号升序排序)
	 * @param baseId
	 * @return 内设机构信息
	 */
	//@Query(value = "select * from ofs_department a where a.org_id=:baseId and a.parent_id = '-1' order by a.dep_num asc", nativeQuery = true)
	@Query("select a from DepartmentEntity a where a.baseId=?1 and a.parentId = '-1' order by a.depNum asc")
	public List<DepartmentEntity> findLevelOneByBaseId(@Param("baseId") Integer baseId);
	
	/**
	 * 根据parentId查询该机构的List信息
	 * @param parentId
	 * @return 内设机构列表信息
	 */
	public List<DepartmentEntity> findByParentId(@Param("parentId") Integer parentId);
	
	/**
	 * 根据baseId查询该机构的一级内设机构信息(并按照内设顺序号升序排序)
	 * @param parentId
	 * @return 内设机构信息
	 */
	public List<DepartmentEntity> findByParentIdOrderByDepNumAsc(@Param("parentId") Integer parentId);

	/**
	 * 根据baseId查询该机构对应内设机构的最大DepNum
	 * @param baseId
	 * @return 内设机构信息
	 */
	@Query(value = "select nvl(max(a.DEP_NUM),0) from OFS_DEPARTMENT a where a.ORG_ID=:baseId", nativeQuery = true)
	public int findMaxDepNumByOrgId(@Param("baseId") Integer baseId);
	
	/**
	 * 根据baseID删除内设机构信息
	 * @param baseId
	 * @return void
	 */
	@Query(value = "delete  from ofs_department de where de.org_id= :baseId", nativeQuery = true)
	@Modifying
	void deleteByBaseId(@Param("baseId") Integer baseId);
	
	/**
	 * 根据baseId查询该机构对应内设机构的最大DepNum
	 * @param baseId
	 * @return 内设机构信息
	 */
	@Query(value = "select a.* from OFS_DEPARTMENT a where a.ORG_ID=:baseId and a.parent_id=:parentId order by a.dep_num asc", nativeQuery = true)
	public List<DepartmentEntity> findByBIdAndPId(@Param("baseId") Integer baseId, @Param("parentId") Integer parentId);
	
	//▼▼▼▼▼▼▼▼权责用DAO▼▼▼▼▼▼▼▼
	@Query(value = "select distinct bean.* from ael_items_dept ct left join ofs_department bean on ct.dept_code=bean.dep_code where ct.item_id=:itemId and bean.org_id=:orgId", nativeQuery = true)
	public List<DepartmentEntity> findByItemIdAndOrgId(@Param("itemId") Integer itemId,@Param("orgId") Integer orgId);

	@Query(value = "select distinct bean.* from ofs_department bean where bean.dep_code=:depCode and bean.org_id=:orgId", nativeQuery = true)
	public List<DepartmentEntity> findByCode(@Param("depCode") String depCode, @Param("orgId") Integer orgId);

	@Query(value = "select bean.* from ofs_department bean where bean.org_id=:baseId", nativeQuery = true)
	public List<DepartmentEntity> findByCodeId1(@Param("baseId") Integer baseId);

	@Query(value = "select bean.* from ofs_department bean where bean.org_id=:baseId and bean.id <> :depCode", nativeQuery = true)
	public List<DepartmentEntity> findByCodeId2(@Param("depCode") String depCode, @Param("baseId") Integer baseId);
	//▲▲▲▲▲▲▲▲权责用DAO▲▲▲▲▲▲▲▲
}
